﻿/*Author：Lary Pan(itolmqj123@gmail.com)
 *Date  ：2016/8/14
 *Tips  ：数据库相关操作
 *         支持类类型  int/float/string/int[]/float[]/string[]/bool
 * */

using UnityEngine;

using Mono.Data.Sqlite;
using System;

public class DBHelper
{
    private SqliteConnection dbConn;
    private SqliteCommand dbCommand;
    private SqliteDataReader reader;


    public DBHelper(string dbName = null)
    {
        if (string.IsNullOrEmpty(dbName))
        {
            dbName = Application.productName;
        }
        _OpenDB(dbName);
    }

    private void _OpenDB(string dbName)
    {
        try
        {
            dbConn = new SqliteConnection();
            SqliteConnectionStringBuilder connsb = new SqliteConnectionStringBuilder();
            connsb.DataSource = string.Format("{0}.db", dbName);
            //connsb.Password = "";
            Debug.Log(connsb.ToString());
            dbConn.ConnectionString = connsb.ToString();
            dbConn.Open();
            Debug.Log("Conencted to DB.");

            _ExecuteSQL("CREATE TABLE IF NOT EXISTS data (key TEXT, value TEXT)");
        }
        catch (Exception e)
        {
            Debug.LogError(e.StackTrace);
            
        }
    }
    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    public void CloseDB()
    {
        //销毁Command
        if (dbCommand != null)
        {
            dbCommand.Cancel();
            dbCommand = null;
        }

        //销毁Reader
        if (reader != null)
        {
            reader.Close();
            reader = null;
        }

        //销毁Connection
        if (dbConn != null)
        {
            dbConn.Close();
            dbConn = null;
        }
        Debug.Log("Disconnected from DB.");
    }

    private SqliteDataReader _ExecuteSQL(string sqlQuery)
    {
        dbCommand = dbConn.CreateCommand();
        dbCommand.CommandText = sqlQuery;
        Debug.LogFormat("DB Execute SQL:{0}", sqlQuery);
        return dbCommand.ExecuteReader();
    }

    /// <summary>
    /// 查询
    /// </summary>
    /// <param name="key"></param>
    /// <returns></returns>
    public string Query(string key)
    {
        string sql = string.Format("SELECT value FROM data where key='{0}'", key);
        reader = _ExecuteSQL(sql);
        if (reader.Read())
        {
            string value = reader.GetString(0);
            Debug.LogFormat("query----->{0} {1}", key, value);
            return value;
        }
        return string.Empty;
    }


    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="key"></param>
    /// <param name="value"></param>
    public void Update(string key, string value)
    {
        string sql  = string.Format("SELECT count(*) FROM data where key='{0}'", key);
        reader      = _ExecuteSQL(sql);

        if (reader.Read())
        {
            int num = reader.GetInt16(0);
            if (num == 0)
            {
                sql = string.Format("INSERT INTO data(key, value) VALUES ('{0}', '{1}')", key, value);
            }
            else
            {
                sql = string.Format("UPDATE data SET value='{0}' WHERE key='{1}'", value, key);
            }
            _ExecuteSQL(sql);
        }
    }


    /// <summary>
    /// 开启事务
    /// </summary>
    public void Begin()
    {
        _ExecuteSQL("BEGIN");
    }

    /// <summary>
    /// 提交事件
    /// </summary>
    public void Commit()
    {
        _ExecuteSQL("COMMIT");
    }
}
   
	

